﻿Public Class clsPgdMes

    Public Function getPgdMesDetailsListByMaterialCode(MaterialCode As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT a.sys_id,a.oper_date,a.mes_project_name,a.mes_project_code FROM sf.b_pgd_mes a "
        szSql &= "left join sf.b_pgd_mes_details b on a.sys_id = b.b_pgd_mes_sys_id "
        szSql &= "left join sf.t_mst_jp c on b.material_code = c.material_code and c.validity = '1' "
        szSql &= "where 1 = 1 and a.finish_count < a.oper_count_all and oper_date >= ? and b.material_code = ? "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, "2022/10/8", MaterialCode)
        Return dt
    End Function

    Public Function insert(en As entityPgdMes) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            If en.lhj = Nothing Then
                szSql &= "INSERT INTO sf.b_pgd_mes(oper_date, mes_project_code, mes_project_name, support_model, type_value, oper_count, note, workshop_id, validity, status, origin, operator, update_time) "
                szSql &= "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , (SELECT CURRENT_TIMESTAMP(6))); "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, en.oper_date, en.mes_project_code, en.mes_project_name, en.support_model, en.type_value, en.oper_count, en.note, en.workshop_id, en.validity, en.status, en.origin, en.Ope)
            Else
                szSql &= "INSERT INTO sf.b_pgd_mes(oper_date, start_time, end_time, mes_project_code, mes_project_name, support_model, type_value, oper_count,lhj, workshop_id, validity, status, origin, operator, update_time, pgd_code, jp_type, lhj_type) "
                szSql &= "VALUES ( ? ,? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , (SELECT CURRENT_TIMESTAMP(6)), ?, ?, ?); "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, en.oper_date, en.start_time, en.end_time, en.mes_project_code, en.mes_project_name, en.support_model, en.type_value, en.oper_count, en.lhj, en.workshop_id, en.validity, en.status, en.origin, en.Ope, en.pgd_code, en.jp_type, en.lhj_type)
                szSql = String.Empty
                szSql &= "SELECT * FROM sf.b_pgd_mes_project_auto_number where 1 = 1 and mes_project_name = ? "
                'szSql &= " and mes_project_code = ?"
                Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, en.mes_project_name)
                'Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, en.mes_project_name, en.mes_project_code)
                If dt.Rows.Count = 0 Then
                    szSql = String.Empty
                    szSql &= "INSERT INTO sf.b_pgd_mes_project_auto_number(mes_project_code, mes_project_name, no_value, update_time) "
                    szSql &= "VALUES ( ? , ? , ? , (SELECT CURRENT_TIMESTAMP(6))); "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, en.mes_project_code, en.mes_project_name, 0)
                End If
            End If
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function update(en As entityPgdMes) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            If en.lhj = Nothing Then
                szSql &= "update sf.b_pgd_mes set oper_date = ?, mes_project_code = ?, mes_project_name = ?, support_model = ? , type_value = ?, oper_count = ?, note = ?, workshop_id = ?, validity = ?, status = ? , origin = ?, operator = ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " where pgd_code = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, en.oper_date, en.mes_project_code, en.mes_project_name, en.support_model, en.type_value, en.oper_count, en.note, en.workshop_id, en.validity, en.status, en.origin, en.Ope, en.pgd_code)
            Else
                szSql &= "update sf.b_pgd_mes set oper_date = ?, start_time = ?, end_time = ?, mes_project_code = ?, mes_project_name = ?, support_model = ?, type_value = ?, oper_count = ?,lhj = ?, workshop_id = ?, validity = ?, status = ?, origin = ?, operator = ?, update_time = (SELECT CURRENT_TIMESTAMP(6)), jp_type = ?, lhj_type = ? "
                szSql &= " where pgd_code = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, en.oper_date, en.start_time, en.end_time, en.mes_project_code, en.mes_project_name, en.support_model, en.type_value, en.oper_count, en.lhj, en.workshop_id, en.validity, en.status, en.origin, en.Ope, en.jp_type, en.lhj_type, en.pgd_code)
                szSql = String.Empty
                szSql &= "SELECT * FROM sf.b_pgd_mes_project_auto_number where 1 = 1 and mes_project_name = ? "
                'szSql &= " and mes_project_code = ?"
                Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, en.mes_project_name)
                'Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, en.mes_project_name, en.mes_project_code)
                If dt.Rows.Count = 0 Then
                    szSql = String.Empty
                    szSql &= "INSERT INTO sf.b_pgd_mes_project_auto_number(mes_project_code, mes_project_name, no_value, update_time) "
                    szSql &= "VALUES ( ? , ? , ? , (SELECT CURRENT_TIMESTAMP(6))); "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, en.mes_project_code, en.mes_project_name, 0)
                End If
            End If
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function insertxjORyzz(en As entityPgdMes) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "INSERT INTO sf.b_pgd_mes(oper_date, mes_project_code, mes_project_name, support_model, type_value, oper_count, note, workshop_id, validity, status, origin, operator, update_time, jp_type, pgd_code) "
            szSql &= "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,? , (SELECT CURRENT_TIMESTAMP(6)),?,?); "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, en.oper_date, en.mes_project_code, en.mes_project_name, en.support_model, en.type_value, en.oper_count, en.note, en.workshop_id, en.validity, en.status, en.origin, en.Ope, en.jp_type, en.pgd_code)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function updatexjORyzz(en As entityPgdMes) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "update sf.b_pgd_mes set oper_date = ?, mes_project_code = ?, mes_project_name = ?, support_model = ? , type_value = ?, oper_count = ?, note = ?, workshop_id = ?, validity = ?, status = ? , origin = ?, operator = ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " where pgd_code = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, en.oper_date, en.mes_project_code, en.mes_project_name, en.support_model, en.type_value, en.oper_count, en.note, en.workshop_id, en.validity, en.status, en.origin, en.Ope, en.pgd_code)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function getProjectListB(b_pgd_mes_sys_id As String, material_code As String) As DataTable
        Dim szSql As String = "select * from sf.b_pgd_mes_details WHERE b_pgd_mes_sys_id = ? and material_code = ? "
        Return DBacs.ExecuteSql(szSql, -1, b_pgd_mes_sys_id, material_code)
    End Function

    Public Function getList(sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_pgd_mes where 1 = 1 and sys_id = ? "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id)
        Return dt
    End Function

    Public Function getListByPgd(pgd_code As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_pgd_mes where 1 = 1 and pgd_code = ? "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, pgd_code)
        Return dt
    End Function


    Public Function getListXj(workshop_id As Integer, mis_name As String, startTime As String, endTime As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT a.*,b.jp_layer_num,b.mould_no,b.prod_spec_name,c.type,c.spec,c.model FROM sf.b_pgd_mes a "
        szSql &= " left join sf.t_prod_spec b on a.support_model = b.prod_spec_id "
        szSql &= " left join sf.t_mst_jp c on a.type_value = c.material_code "
        szSql &= " where 1 = 1 and a.workshop_id = ?"
        If mis_name <> "" Then
            szSql &= " and a.mes_project_name like ?"
        End If
        If startTime <> "" Then
            szSql &= "  and a.oper_date >= ?"
        End If
        If endTime <> "" Then
            szSql &= "  and a.oper_date <= ?"
        End If
        szSql &= "  ORDER BY a.oper_date ASC"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, workshop_id, mis_name, startTime, endTime)
        Return dt
    End Function


    Public Function getList(workshop_id As Integer, mis_name As String, startTime As String, endTime As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT a.*,b.jp_layer_num,b.mould_no,b.prod_spec_name,c.name,c.spec,c.model FROM sf.b_pgd_mes a "
        szSql &= " left join sf.t_prod_spec b on a.support_model = b.prod_spec_id "
        szSql &= " left join sf.t_mst_prod c on a.type_value = c.material_code "
        szSql &= " where 1 = 1 and a.workshop_id = ?"
        If mis_name <> "" Then
            szSql &= " and a.mes_project_name like ?"
        End If
        If startTime <> "" Then
            szSql &= "  and a.oper_date >= ?"
        End If
        If endTime <> "" Then
            szSql &= "  and a.oper_date <= ?"
        End If
        szSql &= "  ORDER BY a.oper_date ASC"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, workshop_id, mis_name, startTime, endTime)
        Return dt
    End Function

    Public Function getList(workshop_id As Integer, validity As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT a.*,b.jp_layer_num,b.mould_no,b.prod_spec_name FROM sf.b_pgd_mes a "
        szSql &= " left join sf.t_prod_spec b on a.support_model = b.prod_spec_id "
        szSql &= " where oper_date>=current_date and a.workshop_id = ? and a.validity = ?"
        szSql &= " order by a.sys_id"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, workshop_id, validity)
        Return dt
    End Function

    Public Function getList() As DataTable
        Dim szSql As String = "SELECT * FROM sf.b_pgd_mes_project_auto_number order by update_time desc"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        Return dt
    End Function
    Public Function updatevalidity(sys_id As String, status As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        Try
            szSql &= "update sf.b_pgd_mes set validity = 0 where sys_id = ? and status = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, sys_id, status)
        Catch ex As Exception

        End Try
        Return rtn
    End Function

    Public Function delete(sys_id As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        szSql &= "delete from sf.b_pgd_mes where sys_id = ? "
        rtn = DBacs.ExecuteUpdateSql(szSql, -1, sys_id)
        Return rtn
    End Function

    Public Function updatenovalue(sys_id As String, no_value As Integer) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        Try
            szSql &= "update sf.b_pgd_mes_project_auto_number set no_value = ? where sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, no_value, sys_id)
        Catch ex As Exception

        End Try
        Return rtn
    End Function

    Public Function updateChangeShiftsContent(sys_id As String, content As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        Try
            szSql &= "update sf.b_pgd_mes set change_shifts_content = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, content, sys_id)
        Catch ex As Exception
            rtn = -2
        End Try
        Return rtn
    End Function

End Class
